// server.js

const express = require('express');
const cors = require('cors');
const http = require('https');
// const fetch = require('node-fetch');
// import fetch from 'node-fetch';
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const Redis = require('ioredis');
const app = express();
const port = 30901;
app.use(cors());
app.use(express.json());

// 确保目录存在
const urlDir = 'http://192.168.1.17:8080/apkh5/testcenter/';
const uploadDir = '/usr/local/nginx/html/apkh5/testcenter/';
if (!fs.existsSync(uploadDir)) {
    fs.mkdirSync(uploadDir, { recursive: true }); // recursive: true 确保可以创建多级目录
}

// 配置 multer
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
        cb(null, uploadDir); // 上传的文件保存到目标目录
    },
    filename: (req, file, cb) => {
        const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1e9);
        const fileNameWithoutExt = path.basename(file.originalname, path.extname(file.originalname)); // 获取不带后缀的文件名
        cb(null, `${fileNameWithoutExt}-${uniqueSuffix}${path.extname(file.originalname)}`);
        // cb(null, `${file.fieldname}-${uniqueSuffix}${path.extname(file.originalname)}`);
    },
});

// 连接到 Redis
const redis = new Redis();
// const redis = new Redis({
//     port: 6379,              // Redis 端口
//     host: '127.0.0.1',       // Redis 主机
//     password: '123456',      // Redis 密码
//     db: 0                    // 默认数据库 0
// });

// 设置请求限制次数
const MAX_REQUESTS_PER_DAY = 4000;
const RATE_LIMIT_KEY = 'fixed_rate_limit_key'; // 固定的 rate limit key

// 中间件：限制每日请求次数
const rateLimitMiddleware = async (req, res, next) => {
    const today = new Date().toISOString().slice(0, 10);
    const redisKey = `${RATE_LIMIT_KEY}:${today}`;
    console.log(`redisKey:${redisKey}`)
    const requestCount = await redis.get(redisKey);

    if (requestCount && parseInt(requestCount) >= MAX_REQUESTS_PER_DAY) {
        // 如果请求次数超过限制，返回 429 错误
        let resObj = {};
        resObj.code = "429";
        resObj.data = { table: "", list: [] };
        resObj.msg = "Daily request limit";
        return res.status(200).send(resObj);
    }

    // 增加请求次数，并设置过期时间为 24 小时后
    await redis.incr(redisKey); // 增加请求次数
    await redis.expire(redisKey, 24 * 60 * 60); // 设置过期时间为 24 小时

    next();
};

// 使用中间件
app.use(rateLimitMiddleware);

// 数据库连接配置-肯尼亚
const mysql = require('mysql');
const pool = mysql.createPool({
    host: '159.138.171.58',
    port: '8807',
    user: 'Wenjie_kny_read',
    database: 'kenniya',
    password: 'tTJ*70fmJ4i6JE6!0sS$3xf7'
});

// 数据库连接配置-科特
const poolFcfa = mysql.createPool({
    host: '159.138.162.31',
    port: '7706',
    user: 'Wenjie_kt_read',
    database: 'ketediwa',
    password: 'e1A8iP^1Qzh(zK4*JAD&OSOT'
});

// 数据库连接配置-加纳
const poolGha = mysql.createPool({
    host: '159.138.162.31',
    port: '7706',
    user: 'Wenjie_jn_read',
    database: 'jiana',
    password: 'F^*2XiDfIWtYEqn8I1DKPoWy'
});

// 数据库连接配置-赞比亚
const poolZmb = mysql.createPool({
    host: '110.238.79.79',
    port: '9906',
    user: 'Wenjie_zby_read',
    database: 'zambia_product',
    password: 'CQvtxhQz)*ozdE#up#$r)t9K'
});

// 数据库连接配置-喀唛隆
const poolXaf = mysql.createPool({
    host: '110.238.74.228',
    port: '5506',
    user: 'Wenjie_kml_read',
    database: 'kamailong_product',
    password: '13C1!Z#cH~^0%9QyHXqpY84x'
});

// 数据库连接配置-乌干达
const poolUgx = mysql.createPool({
    host: '159.138.171.58',
    port: '8807',
    user: 'Wenjie_ugd_read',
    database: 'uganda',
    password: '1g0A4M#KsBWh$SyfuXlAP#6'
});

// 数据库连接配置-坦桑尼亚
// const mysqlTanz = require('mysql');
const poolTanz = mysql.createPool({
    host: '159.138.171.58',
    port: '8807',
    user: 'Wenjie_tsny_read',
    database: 'tangsang_product',
    password: '04t4Ed3$cYo#ix4$a5si(ErBc'
});

// 数据库连接配置-塞内加尔
// const mysqlSenegal = require('mysql');
const poolSenegal = mysql.createPool({
    host: '159.138.162.31',
    port: '7706',
    user: 'Wenjie_sn_read',
    database: 'senegal_product',
    password: '*DfGH!o&LN2hMQ)tEHIa^v81'
});

// 数据库连接配置-贝宁
const poolBenin = mysql.createPool({
    host: '110.238.74.228',
    port: '5506',
    user: 'Wenjie_bn_read',
    database: 'benin_product',
    password: 'p2V!kd$Ly1h*LSONx%)W#ahD'
});

// 数据库连接配置-刚果金
const poolCongo = mysql.createPool({
    host: '110.238.74.228',
    port: '5506',
    user: 'Wenjie_ggj_read',
    database: 'gangguo_product',
    password: 'Iygh04B4(J*CH^FNy^voHas)'
});

// 数据库连接配置-莫桑比克
const poolMozambique = mysql.createPool({
    host: '110.238.79.79',
    port: '9906',
    user: 'Wenjie_msbk_read',
    database: 'mozambq_product',
    password: 'L!BegL6Gow)hSFbZ5jJ5n#Gy'
});

// 数据库连接配置-斯里兰卡
const poolSrilanka = mysql.createPool({
    host: '121.91.158.109',
    port: '2206',
    user: 'Wenjie_sllk_read',
    database: 'srilanka_product',
    password: ')Uokz9zse3iD7Z2wyZ#y&jtg'
});

// 数据库连接配置-柬埔寨
const poolCambodia = mysql.createPool({
    host: '119.12.172.196',
    port: '6606',
    user: 'Wenjie_cambodia_read',
    database: 'cambodia_product',
    password: 'MB!YoKN2&Lk4EJpzn=UWLZ'
});

// 创建 Redis 客户端
// const redisClient = redis.createClient();

// // 连接 Redis
// redisClient.on('connect', () => {
//     console.log('Connected to Redis');
// });

// redisClient.on('error', (err) => {
//     console.error('Redis error:', err);
// });

// 国家列表
const ksh = "ksh";
const gha = "gha";
const ugx = "ugx";
const fcfa = "fcfa";
const tanz = "tanz";
const xaf = "xaf";
const zmb = "zmb";
const senegal = "senegal";
const benin = "benin";
const congo = "congo";
const srilanka = "srilanka";
const mozambique = "mozambique";
const cambodia = "cambodia";
const countryList = [ksh, gha, ugx, fcfa, tanz, xaf, zmb, senegal, benin, congo, srilanka, mozambique, cambodia];

app.listen(port, () => {
    console.log(`Server is running on port ${port}`);
});

// 限制文件大小为 20MB，并只允许上传 .apk 文件
const upload = multer({
    storage,
    limits: { fileSize: 20 * 1024 * 1024 }, // 20MB
    fileFilter: (req, file, cb) => {
        if (path.extname(file.originalname).toLowerCase() === '.apk') {
            cb(null, true); // 允许上传
        } else {
            cb(new Error('Only .apk files are allowed'), false); // 拒绝上传
        }
    },
});

// 上传文件接口
app.post('/api/apkupload', upload.single('file'), (req, res) => {
    try {
        if (!req.file) {
            return res.status(400).send({
                code: "0",
                data: {},
                msg: "No file uploaded",
            });
        }

        const fileName = req.file.filename;
        res.status(200).send({
            code: "0",
            data: {
                filename: req.file.filename,
                url: `${urlDir}${fileName}`
            },
            msg: "File uploaded successfully",
        });
    } catch (err) {
        console.error(err);
        res.status(500).send({
            code: "0",
            data: {},
            msg: err.message || "File upload failed",
        });
    }
});

// 错误处理中间件
app.use((err, req, res, next) => {
    if (err instanceof multer.MulterError) {
        if (err.code === "LIMIT_FILE_SIZE") {
            return res.status(400).send({
                code: "0",
                data: {},
                msg: "File size exceeds the limit of 20MB",
            });
        }
    } else if (err.message === 'Only .apk files are allowed') {
        return res.status(400).send({
            code: "0",
            data: {},
            msg: "Invalid file type. Only .apk files are allowed",
        });
    }
    next(err);
});

// GET 请求路由
app.get('/api/getdata', (req, res) => {
    console.log("======get requst now");
    return res.send('Data get successfully');
});

// 处理来自前端的 POST 请求 - 修改订单状态
app.post('/api/changeOrderStatus', (req, res) => {
    // 获取前端发送的数据
    const data = req.body;
    console.log("req-change-order-status:");
    console.log(data);

    if (!data) {
        let resObj = {};
        resObj.code = "0";
        resObj.data = {};
        resObj.msg = "Error get data into database";
        return res.status(200).send(resObj);
    }

    let country = data.country || "";
    if (country.length === 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = {};
        resObj.msg = "Invild params: country is null";
        return res.status(200).send(resObj);
    }

    let orderId = data.orderId || "";
    if (orderId.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = {};
        resObj.msg = "Invild params: orderId is null";
        return res.status(200).send(resObj);
    }

    let type = data.type || "";
    if (type.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = {};
        resObj.msg = "Invild params: type is null";
        return res.status(200).send(resObj);
    }

    const postData = {
        orderId: orderId,
        type: type
    };

    const headers = {
        'Content-Type': 'application/json'
    }

    const requestOptions = {
        method: 'POST',
        headers: headers,
        body: JSON.stringify(postData)
    };

    let linkUrl = ""
    if (country === ksh) {
        linkUrl = "https://www.globalpwa.online/kny-front/v1/modifyTestOrderStatus";
        headers.country = "kenya"
    }
    else if (country === gha) {
        linkUrl = "https://www.globalpwa.online/jn-front/v1/modifyTestOrderStatus";
        headers.country = "ghana"
    }
    else if (country === ugx) {
        linkUrl = "https://www.globalpwa.online/wgd-front/v1/modifyTestOrderStatus";
        headers.country = "uganda"
    }
    else if (country === fcfa) {
        linkUrl = "https://www.globalpwa.online/kt-front/v1/modifyTestOrderStatus";
        headers.country = "cotediva"
    }
    else if (country === tanz) {
        linkUrl = "https://www.globalpwa.online/tsny-front/v1/modifyTestOrderStatus";
        headers.country = "tanzania"
    }
    else if (country === xaf) {
        linkUrl = "https://www.globalpwa.online/kml-front/v1/modifyTestOrderStatus";
        headers.country = "cameroon"
    }
    else if (country === zmb) {
        linkUrl = "https://www.globalpwa.online/zby-front/v1/modifyTestOrderStatus";
        headers.country = "zambia"
    }
    else if (country === senegal) {
        linkUrl = "https://www.globalpwa.online/snje-front/v1/modifyTestOrderStatus";
        headers.country = "senegal"
    }
    else if (country === benin) {
        linkUrl = "https://www.globalpwa.online/bn-front/v1/modifyTestOrderStatus";
        headers.country = "benin"
    }
    else if (country === congo) {
        linkUrl = "https://www.globalpwa.online/ggj-front/v1/modifyTestOrderStatus";
        headers.country = "congo"
    }
    else if (country === mozambique) {
        linkUrl = "https://www.globalpwa.online/front/v1/modifyTestOrderStatus";
        headers.country = "mozambique"
    }
    else if (country === srilanka) {
        linkUrl = "https://www.globalpwa.online/front/v1/modifyTestOrderStatus";
        headers.country = "srilanka"
    }
    else if (country === cambodia) {
        linkUrl = "https://www.globalpwa.online/front/v1/modifyTestOrderStatus";
        headers.country = "cambodia"
    }
    if (linkUrl.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = {};
        resObj.msg = "Invild params: country is not match";
        return res.status(200).send(resObj);
    }

    fetch(linkUrl, requestOptions)
        .then(response => response.json())
        .then(data => {
            console.log(data);
            // { code: '9999', msg: '修改失败', obj: '' }
            const resCode = data.code || "-1";
            const resMsg = data.msg || "";

            let resObj = {};
            resObj.code = resCode;
            resObj.data = {};
            resObj.msg = resMsg;
            return res.status(200).send(resObj);
        })
        .catch(error => {
            console.error(error);

            const resCode = "-1";
            const resMsg = "Request error";

            let resObj = {};
            resObj.code = resCode;
            resObj.data = {};
            resObj.msg = resMsg;
            return res.status(200).send(resObj);
        });
});

// 处理来自前端的 POST 请求 - 查询表状态
app.post('/api/data', (req, res) => {
    // 获取前端发送的数据
    const data = req.body;
    console.log("req-query-table-data:");
    console.log(data);

    if (!data) {
        let resObj = {};
        resObj.code = "0";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Error get data into database";
        return res.status(200).send(resObj);
    }

    let product = data.product || "";
    let mobile = data.mobile || "";
    let token = data.token || "";
    let userId = data.userId || "";
    let advertId = data.advertId || "";
    let androidId = data.androidId || "";
    let orderId = data.orderId || "";
    let startDate = data.startDate || "";
    let table = data.table || "";
    let country = data.country || "";

    if (country.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: country is null";
        return res.status(200).send(resObj);
    }

    if (countryList.indexOf(country) === -1) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: country not match";
        return res.status(200).send(resObj);
    }

    if (product.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: product is null";
        return res.status(200).send(resObj);
    }

    if (table.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: table is null";
        return res.status(200).send(resObj);
    }

    // 处理请求逻辑
    if (table === "app_config") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTAppShowConfigRecord(data, onRecordResult);

    }
    else if (table === "sms") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTSmsRecord(data, onRecordResult);

    } else if (table === "whatsapp_sms") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTWhatsappSmsRecord(data, onRecordResult);

    } else if (table === "user") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTUserRecord(data, onRecordResult);

    } else if (table === "user_channel") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTUserChannelRecord(data, onRecordResult);

    } else if (table === "user_contact") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTUserContactRecord(data, onRecordResult);

    } else if (table === "user_wallet") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTUserWalletRecord(data, onRecordResult);

    } else if (table === "order") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTLoanOrderRecord(data, onRecordResult);

    } else if (table === "google_install") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTGoogleInstallRecord(data, onRecordResult);

    } else if (table === "adjust_install") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTAdjustInstallRecord(data, onRecordResult);

    } else if (table === "device") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTDeviceRecord(data, onRecordResult);

    } else if (table === "device_battery") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTDeviceBatteryRecord(data, onRecordResult);

    } else if (table === "device_hardware") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTDeviceHardwareRecord(data, onRecordResult);

    } else if (table === "device_storage") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTDeviceStorageRecord(data, onRecordResult);

    } else if (table === "device_wifi") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTDeviceWifiRecord(data, onRecordResult);

    } else if (table === "device_app") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTDeviceAppRecord(data, onRecordResult);

    } else if (table === "device_sms_fetch") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTDeviceSmsFetchRecord(data, onRecordResult);

    } else if (table === "device_firebase_fetch") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTDeviceFirebaseFetchRecord(data, onRecordResult);

    } else if (table === "user_buried_point") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTUserBuriedPointRecord(data, onRecordResult);

    } else if (table === "user_buried_point_all") {
        let onRecordResult = (trecord) => {
            let trecordF = trecord || [];
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTUserBuriedPointAllRecord(data, onRecordResult);

    }
    else {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: table not match";
        return res.status(200).send(resObj);
    }

});

// 处理来自前端的 POST 请求 - 查询point表状态
app.post('/api/pointdata', (req, res) => {
    // 获取前端发送的数据
    const data = req.body;
    console.log("req-query-point-table-data:");
    console.log(data);

    if (!data) {
        let resObj = {};
        resObj.code = "0";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Error get data into database";
        return res.status(200).send(resObj);
    }

    let product = data.product || "";
    let mobile = data.mobile || "";
    let token = data.token || "";
    let userId = data.userId || "";
    let advertId = data.advertId || "";
    let androidId = data.androidId || "";
    let orderId = data.orderId || "";
    let startDate = data.startDate || "";
    let table = data.table || "";
    let country = data.country || "";

    if (country.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: country is null";
        return res.status(200).send(resObj);
    }

    if (countryList.indexOf(country) === -1) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: country not match";
        return res.status(200).send(resObj);
    }

    if (product.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: product is null";
        return res.status(200).send(resObj);
    }

    if (table.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: table is null";
        return res.status(200).send(resObj);
    }
    if (table === "user_buried_point") {
        let onRecordResult = (trecord, totalPages) => {
            let trecordF = trecord || [];
            let totalPagesF = totalPages || 0;
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF, totalPages: totalPagesF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTUserBuriedPointAllRecord(data, onRecordResult);

    } else if (table === "user_buried_point_all") {
        let onRecordResult = (trecord, totalPages) => {
            let trecordF = trecord || [];
            let totalPagesF = totalPages || 0;
            let resObj = {};
            resObj.code = "0";
            resObj.data = { table: table, list: trecordF, totalPages: totalPagesF };
            resObj.msg = "success";
            return res.status(200).send(resObj);
        };

        combineTableTUserBuriedPointAllRecord(data, onRecordResult);

    }
    else {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: table not match";
        return res.status(200).send(resObj);
    }

});

// 处理来自前端的 POST 请求 - 查询表状态
app.post('/api/customdata', (req, res) => {
    // 获取前端发送的数据
    const data = req.body;
    console.log("req-query-table-data-custom:");
    console.log(data);

    if (!data) {
        let resObj = {};
        resObj.code = "0";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Error get data into database";
        return res.status(200).send(resObj);
    }

    let excLine = data.excLine || "";
    let country = data.country || "";

    if (excLine.length <= 0) {
        let resObj = {};
        resObj.code = "-1";
        resObj.data = { table: table, list: [] };
        resObj.msg = "Invild params: country is null";
        return res.status(200).send(resObj);
    }

    let onRecordResult = (trecord) => {
        let trecordF = trecord || [];
        let resObj = {};
        resObj.code = "0";
        resObj.data = { table: table, list: trecordF };
        resObj.msg = "success";
        return res.status(200).send(resObj);
    };

    console.log(excLine);
    executeSql(excLine, onRecordResult, country);
});

const cache = new Map();
const getCachedTotalRecords = (key) => {
    if (key && key.length > 0) {
        return cache.get(key) || "";
    }
    return "";
};
const setCachedTotalRecords = (key, value) => {
    if (key && key.length > 0) {
        cache.set(key, value || "");
    }
};

const combineTableTAppShowConfigRecord = (data, callback) => {
    let product = data.product || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select * from t_app_show_config where product = '" + product + "'";

    sqlString += " order by create_time desc limit 1";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTSmsRecord = (data, callback) => {
    let product = data.product || "";
    let mobile = data.mobile || "";
    let startDate = data.startDate || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }
    if (mobile.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select product, mobile, content, create_time from t_sms_record where product = '" + product + "'";

    if (mobile.length > 0) {
        // if (country === benin) {
        //     if (!mobile.startsWith("01")) {
        //         mobile = "01" + mobile;
        //     }
        // }
        sqlString += " and mobile = '" + mobile + "'";
    }

    if (startDate.length > 0) {
        sqlString += " and create_time >= '" + startDate + "'";
    }
    else {
        sqlString += " and create_time >= CURRENT_DATE";
    }

    sqlString += " order by create_time desc limit 10";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTWhatsappSmsRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let startDate = data.startDate || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }
    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select id, product, original_mobile, user_id, message_code, send_type, status, pass_time, create_time from t_whatapp_message_record where product = '" + product + "'";

    if (userId.length > 0) {
        // if (country === benin) {
        //     if (!mobile.startsWith("01")) {
        //         mobile = "01" + mobile;
        //     }
        // }
        sqlString += " and user_id = '" + userId + "'";
    }

    if (startDate.length > 0) {
        sqlString += " and create_time >= '" + startDate + "'";

    } else {
        sqlString += " and create_time >= CURRENT_DATE";
    }

    sqlString += " order by create_time desc limit 5";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTUserRecord = (data, callback) => {
    let product = data.product || "";
    let mobile = data.mobile || "";
    let userId = data.userId || "";
    let token = data.token || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select product, user_id, mobile, token, advert_id, android_id, full_name, version, sub_channel_code, create_time from t_user where product = '" + product + "'";

    if ((mobile.length <= 0) && (userId.length <= 0) && (token.length <= 0)) {
        callback && callback(null);
        return;
    }

    if (mobile.length > 0) {
        sqlString += " and mobile = '" + mobile + "'";
    }

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    if (token.length > 0) {
        sqlString += " and token = '" + token + "'";
    }

    sqlString += " order by create_time desc limit 10";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTUserChannelRecord = (data, callback) => {
    let product = data.product || "";
    let mobile = data.mobile || "";
    let userId = data.userId || "";
    let token = data.token || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }
    if ((mobile.length <= 0) && (userId.length <= 0) && (token.length <= 0)) {
        callback && callback(null);
        return;
    }

    let sqlString = "select product, mobile, user_id, token, birth_date, android_id, advert_id, channel_code, sub_channel_code, create_time from t_user where product = '" + product + "'";

    if (mobile.length > 0) {
        sqlString += " and mobile = '" + mobile + "'";
    }

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    if (token.length > 0) {
        sqlString += " and token = '" + token + "'";
    }

    sqlString += " order by create_time desc limit 5";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTUserContactRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }
    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select * from t_user_contact where product = '" + product + "'";

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 20";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTUserWalletRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }
    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select * from t_user_bank_card where product = '" + product + "'";

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 10";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTLoanOrderRecord = (data, callback) => {
    let userId = data.userId || "";
    let orderId = data.orderId || "";
    let country = data.country || "";

    if (country.length <= 0) {
        callback && callback(null);
        return;
    }

    if ((orderId.length <= 0) && (userId.length <= 0)) {
        callback && callback(null);
        return;
    }

    // if (orderId.length <= 0) {
    //     callback && callback(null);
    //     return;
    // }

    let sqlString = "SELECT t_loan_apply.product, t_loan_apply.user_id, t_loan_apply.order_id, t_loan_apply.status as apply_status, t_loan_order.status as order_status " +
        "FROM t_loan_apply " +
        "LEFT JOIN t_loan_order " +
        "ON t_loan_apply.order_id = t_loan_order.order_id ";

    if (userId.length > 0) {
        sqlString += "WHERE t_loan_apply.user_id = '" + userId + "'";
        if (orderId.length > 0) {
            sqlString += " and t_loan_apply.order_id = '" + orderId + "'";
        }
    } else if (orderId.length > 0) {
        sqlString += "WHERE t_loan_apply.order_id = '" + orderId + "'";
        if (userId.length > 0) {
            sqlString += " and t_loan_apply.user_id = '" + userId + "'";
        }
    }
    sqlString += " order by t_loan_apply.create_time desc limit 5";

    // let sqlString = "select product, user_id, order_id, status from t_loan_apply ";
    // if (userId.length > 0) {
    //     sqlString += " where user_id = '" + userId + "'";

    //     if (orderId.length > 0) {
    //         sqlString += " and order_id = '" + orderId + "'";
    //     }
    // }

    // else if (orderId.length > 0) {
    //     sqlString += " where order_id = '" + orderId + "'";

    //     if (userId.length > 0) {
    //         sqlString += " and user_id = '" + userId + "'";
    //     }
    // }

    // sqlString += " order by create_time desc limit 10";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTGoogleInstallRecord = (data, callback) => {
    let product = data.product || "";
    let advertId = data.advertId || "";
    let androidId = data.androidId || "";
    let startDate = data.startDate || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if ((advertId.length <= 0) && (androidId.length <= 0)) {
        callback && callback(null);
        return;
    }

    let sqlString = "select /*+ max_execution_time(50000)*/ product, advert_id, android_id, channel_code, sub_channel_code, version, create_time from t_channel_install_temp where product = '" + product + "'";

    if (advertId.length > 0) {
        sqlString += " and advert_id = '" + advertId + "'";
    }
    else if (androidId.length > 0) {
        sqlString += " and android_id = '" + androidId + "'";
    }

    if (startDate.length > 0) {
        sqlString += " and create_time >= '" + startDate + "'";
    }

    sqlString += " order by create_time desc limit 10";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTAdjustInstallRecord = (data, callback) => {
    let product = data.product || "";
    let advertId = data.advertId || "";
    let startDate = data.startDate || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if (advertId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select /*+ max_execution_time(50000)*/ product, token, adid, advert_id, remark, create_time from `t_adjust_install_temp` where product = '" + product + "'";

    if (advertId.length > 0) {
        sqlString += " and `advert_id` = '" + advertId + "'";
    }

    if (startDate.length > 0) {
        sqlString += " and create_time >= '" + startDate + "'";
    }
    else {
        sqlString += " and create_time >= CURRENT_DATE";
    }

    sqlString += " order by `create_time` desc limit 10";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTDeviceRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select * from t_user_device where product = '" + product + "'";

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 5";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTDeviceBatteryRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select * from t_user_device_battery where product = '" + product + "'";

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 5";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTDeviceHardwareRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select * from t_user_device_hardware where product = '" + product + "'";

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 5";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTDeviceStorageRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select * from t_user_device_storage where product = '" + product + "'";

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 5";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTDeviceWifiRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select * from t_user_device_wifi where product = '" + product + "'";

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 5";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTDeviceAppRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let subTabIndex = userId.substring(userId.length - 1);

    let sqlString = "select * from t_user_app_" + subTabIndex + " where product = '" + product + "'";
    if (country === gha) {
        sqlString = "select * from t_user_app where product = '" + product + "'";
    }

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 20";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTDeviceSmsFetchRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select * from t_user_sms_fetch where product = '" + product + "'";

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 20";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTDeviceFirebaseFetchRecord = (data, callback) => {
    let product = data.product || "";
    let userId = data.userId || "";
    let country = data.country || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if (userId.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlString = "select product, user_id, name, advert_id, android_id, firebase_app_id, ga4_app_instance_id, create_time from t_firebase_record where product = '" + product + "'";

    if (userId.length > 0) {
        sqlString += " and user_id = '" + userId + "'";
    }

    sqlString += " order by create_time desc limit 20";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTUserBuriedPointRecord = (data, callback) => {
    let product = data.product || "";
    let token = data.token || "";
    let advertId = data.advertId || "";
    let startDate = data.startDate || "";
    let country = data.country || "";
    let androidId = data.androidId || "";

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    if ((token.length <= 0) && (advertId.length <= 0)) {
        callback && callback(null);
        return;
    }

    let sqlString = "select /*+ max_execution_time(50000)*/ * from t_user_buried_point_202502  where product = '" + product + "'";

    if (token.length > 0) {
        sqlString += " and user_id = '" + token + "'";
    }

    if (advertId.length > 0) {
        sqlString += " and advert_id = '" + advertId + "'";
    }

    if (androidId.length > 0) {
        sqlString += " and android_id = '" + androidId + "'";
    }

    // 最多只能查7天
    if (startDate.length > 0) {
        let date1 = new Date(startDate);
        let date2 = new Date();

        let date1Time = date1.getTime();
        let date2Time = date2.getTime();

        if (date1Time >= date2Time) {
            sqlString += " and create_time >= '" + startDate + "'";
        } else {
            let timeDifference = date2Time - date1Time;
            let sevenDaysTime = (4 * 24 * 60 * 60 * 1000);
            if (timeDifference > sevenDaysTime) {
                let nowDate = new Date(date2Time - sevenDaysTime);
                let year = nowDate.getFullYear();
                let month = nowDate.getMonth() + 1;
                let monthF = month < 10 ? ('0' + month) : ('' + month);
                let day = nowDate.getDate();
                let dayF = day < 10 ? ('0' + day) : ('' + day);
                let startDateF = year + "-" + monthF + "-" + dayF + " 00:00:00";
                sqlString += " and create_time >= '" + startDateF + "'";

            } else {
                sqlString += " and create_time >= '" + startDate + "'";
            }
        }
    } else {
        let nowDate = new Date();
        let year = nowDate.getFullYear();
        let month = nowDate.getMonth() + 1;
        let monthF = month < 10 ? ('0' + month) : ('' + month);
        let day = nowDate.getDate();
        let dayF = day < 10 ? ('0' + day) : ('' + day);
        let startDateF = year + "-" + monthF + "-" + dayF + " 00:00:00";
        sqlString += " and create_time >= '" + startDateF + "'";
    }

    sqlString += " order by create_time desc";

    console.log(sqlString);
    executeSql(sqlString, callback, country);
};

const combineTableTUserBuriedPointAllRecord = (data, callback) => {
    let product = data.product || "";
    let token = data.token || "";
    let advertId = data.advertId || "";
    let androidId = data.androidId || "";
    let startDate = data.startDate || "";
    let endDate = data.endDate || "";
    let country = data.country || "";

    let page = data.page || 1;
    let pageF = (page - 1) * 20;
    let pageSize = data.pageSize || 20;

    if (product.length <= 0) {
        callback && callback(null);
        return;
    }

    let sqlCountString = "select /*+ max_execution_time(180000)*/ count(*) as total_records from t_user_buried_point_202502  where product = '" + product + "'";
    let sqlString = "select /*+ max_execution_time(180000)*/ * from t_user_buried_point_202502  where product = '" + product + "'";


    let cacheKey = "P" + product + "_C" + country;

    if (token.length > 0) {
        sqlString += " and user_id = '" + token + "'";
        sqlCountString += " and user_id = '" + token + "'";
    }
    cacheKey = cacheKey + "_T" + token;

    if (advertId.length > 0) {
        sqlString += " and advert_id = '" + advertId + "'";
        sqlCountString += " and advert_id = '" + advertId + "'";
    }
    cacheKey = cacheKey + "_G" + advertId;

    if (androidId.length > 0) {
        sqlString += " and android_id = '" + androidId + "'";
        sqlCountString += " and android_id = '" + androidId + "'";
    }
    cacheKey = cacheKey + "_A" + androidId;

    cacheKey = cacheKey + "_S" + startDate;
    cacheKey = cacheKey + "_E" + endDate;


    // startDate.length = 0
    // startDate.length > 0
    // startDate.length > 0 && endDate.length > 0

    // endDate.length = 0
    // endDate.length > 0
    // endDate.length > 0 && startDate.length > 0

    // 最多只能查7天
    if ((startDate.length > 0) && (endDate.length <= 0)) {
        let date1 = new Date(startDate);
        let date1Time = date1.getTime();

        let sevenDaysTime = (8 * 24 * 60 * 60 * 1000);
        let nowDate = new Date(date1Time + sevenDaysTime);
        let year = nowDate.getFullYear();
        let month = nowDate.getMonth() + 1;
        let monthF = month < 10 ? ('0' + month) : ('' + month);
        let day = nowDate.getDate();
        let dayF = day < 10 ? ('0' + day) : ('' + day);
        let endDateF = year + "-" + monthF + "-" + dayF;

        sqlString += " and create_time >= '" + startDate + " 00:00:00'";
        sqlCountString += " and create_time >= '" + startDate + " 00:00:00'";

        sqlString += " and create_time <= '" + endDateF + " 23:59:59'";
        sqlCountString += " and create_time <= '" + endDateF + " 23:59:59'";


    } else if ((startDate.length > 0) && (endDate.length > 0)) {
        // 以结束点为主
        if (startDate === endDate) {
            sqlString += " and create_time >= '" + startDate + " 00:00:00'";
            sqlCountString += " and create_time >= '" + startDate + " 00:00:00'";

            sqlString += " and create_time <= '" + endDate + " 23:59:59'";
            sqlCountString += " and create_time <= '" + endDate + " 23:59:59'";

        } else {
            let date1 = new Date(startDate);
            let date2 = new Date(endDate);

            let date1Time = date1.getTime();
            let date2Time = date2.getTime();

            if (date1Time > date2Time) {
                callback && callback(null);
                return;

            } else {
                let timeDifference = date2Time - date1Time;
                let sevenDaysTime = (8 * 24 * 60 * 60 * 1000);
                if (timeDifference > sevenDaysTime) {
                    let nowDate = new Date(date2Time - sevenDaysTime);
                    let year = nowDate.getFullYear();
                    let month = nowDate.getMonth() + 1;
                    let monthF = month < 10 ? ('0' + month) : ('' + month);
                    let day = nowDate.getDate();
                    let dayF = day < 10 ? ('0' + day) : ('' + day);
                    let startDateF = year + "-" + monthF + "-" + dayF;

                    sqlString += " and create_time >= '" + startDateF + " 00:00:00'";
                    sqlCountString += " and create_time >= '" + startDateF + " 00:00:00'";

                    sqlString += " and create_time <= '" + endDate + " 23:59:59'";
                    sqlCountString += " and create_time <= '" + endDate + " 23:59:59'";

                } else {
                    sqlString += " and create_time >= '" + startDate + " 00:00:00'";
                    sqlCountString += " and create_time >= '" + startDate + " 00:00:00'";

                    sqlString += " and create_time <= '" + endDate + " 23:59:59'";
                    sqlCountString += " and create_time <= '" + endDate + " 23:59:59'";
                }
            }
        }
    } else if ((startDate.length <= 0) && (endDate.length > 0)) {
        let date1Time = new Date(endDate).getTime();
        let sevenDaysTime = (8 * 24 * 60 * 60 * 1000);

        let nowDate = new Date(date1Time - sevenDaysTime);
        let year = nowDate.getFullYear();
        let month = nowDate.getMonth() + 1;
        let monthF = month < 10 ? ('0' + month) : ('' + month);
        let day = nowDate.getDate();
        let dayF = day < 10 ? ('0' + day) : ('' + day);
        let startDateF = year + "-" + monthF + "-" + dayF;
        sqlString += " and create_time >= '" + startDateF + " 00:00:00'";
        sqlCountString += " and create_time >= '" + startDateF + " 00:00:00'";

        sqlString += " and create_time <= '" + endDate + " 23:59:59'";
        sqlCountString += " and create_time <= '" + endDate + " 23:59:59'";

    } else {
        // startDate.length = 0
        // endDate.length = 0

        let nowDate = new Date();

        let sevenDaysAgo = new Date();
        sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 7);

        let endYear = nowDate.getFullYear();
        let endMonth = nowDate.getMonth() + 1;
        let endMonthF = endMonth < 10 ? ('0' + endMonth) : ('' + endMonth);
        let endDay = nowDate.getDate();
        let endDayF = endDay < 10 ? ('0' + endDay) : ('' + endDay);
        let endDateF = endYear + "-" + endMonthF + "-" + endDayF;


        let startYear = sevenDaysAgo.getFullYear();
        let startMonth = sevenDaysAgo.getMonth() + 1;
        let startMonthF = startMonth < 10 ? ('0' + startMonth) : ('' + startMonth);
        let startDay = sevenDaysAgo.getDate();
        let startDayF = startDay < 10 ? ('0' + startDay) : ('' + startDay);
        let startDateF = startYear + "-" + startMonthF + "-" + startDayF;

        sqlString += " and create_time >= '" + startDateF + " 00:00:00'" + " and create_time <= '" + endDateF + " 23:59:59'";
        sqlCountString += " and create_time >= '" + startDateF + " 00:00:00'" + " and create_time <= '" + endDateF + " 23:59:59'";
    }

    sqlString += " order by create_time desc";
    sqlCountString += " order by create_time desc";

    sqlString += " limit " + pageSize;
    sqlString += " offset " + pageF;

    console.log(sqlString);

    const checkCacheRecords = getCachedTotalRecords(cacheKey);
    if (checkCacheRecords && checkCacheRecords.length > 0) {
        const totalRecords = parseInt(checkCacheRecords) || 0;
        const totalPages = Math.ceil(totalRecords / pageSize);
        console.log("point count cache: " + totalRecords + " , page: " + totalPages);

        executeSql(sqlString, (pageRecords) => {
            callback && callback(pageRecords, totalPages);
        }, country);

    } else {
        executeSql(sqlCountString, (records) => {
            if (records != null) {
                const totalRecords = records[0].total_records || 0;
                const totalPages = Math.ceil(totalRecords / pageSize);
                console.log("point count: " + totalRecords + " , page: " + totalPages);
                setCachedTotalRecords(cacheKey, totalRecords + "");

                executeSql(sqlString, (pageRecords) => {
                    callback && callback(pageRecords, totalPages);
                }, country);
            }
        }, country);
    }
};

const executeSql = (sqlString, callback, country) => {
    if (country === ksh) {
        pool.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === gha) {
        poolGha.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === ugx) {
        poolUgx.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === fcfa) {
        poolFcfa.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === tanz) {
        poolTanz.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === xaf) {
        poolXaf.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === zmb) {
        poolZmb.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === senegal) {
        poolSenegal.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === benin) {
        poolBenin.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === congo) {
        poolCongo.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === mozambique) {
        poolMozambique.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === srilanka) {
        poolSrilanka.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
    else if (country === cambodia) {
        poolCambodia.getConnection((err, connection) => {
            if (err) {
                // connection.release();
                console.log(err);
                callback && callback(null);
                return null;
            } else {
                connection.query(sqlString, null, (error, results, fields) => {
                    connection.release();
                    if (error) {
                        console.log(error);
                        callback && callback(null);
                        return null;
                    }
                    console.log('Query successly\n' + sqlString);
                    callback && callback(results);
                });
            }
        });
    }
}
